﻿using System;
using System.Collections.Generic;
using System.Text;
//using System.Windows.Forms;
using System.Data.OleDb;
using System.Data.Common;
using System.Data;
using System.Collections;
using System.ComponentModel;
using System.Globalization;
using System.IO;
using System.Runtime.Serialization;
using System.Threading;
using System.Xml;
using System.Xml.Schema;
using System.Xml.Serialization;
namespace Evaluation3.data
{
    // Khai bao lop dataTable dua tren cau truc cua DataTable
    // Tat ca du lieu sau nay se duoc luu tru tren lop nay thay vi dung DataSet or DataTable
    // Bat buoc cac lop sau phai ke thua lop base nay
    public abstract class MyDataTable:DataTable
    {
        //DataTable d = new DataTable(

#region "Others Define"
        //Day la enum dinh nghia viec thao tac voi CSDL
        //Voi 
        //PDataTableAction.AllChanges:   Lay tat ca cac thay doi tren CSDL cua bang do, thuong su dung khi cap nhat tren Grid, tu dong MyDataTable se GEN ra cac cau lenh: Insert, Update, Delete khi duoc Mapping Columns
        //PDataTableAction.AddNew:       Lay cac thay doi tao ra do viec them moi ban ghi
        //PDataTableAction.Update:       Lay cac thay doi tao ra do viec sua doi ban ghi
        //PDataTableAction.Delete:       Lay cac thay doi tao ra do viec xoa ban ghi
        public enum DataTableAction
        {
            AllChanges = 0,
            AddNew = 1,
            Update = 2,
            Delete = 4,
        };
        
#endregion

#region " Private variables"
        //Bien nay dung de luu tru mot OleDbConnection cua doi tuong.
        //Dung de thao tac voi CSDL nhu Connect, Addnew, Delete, Update
        private OleDbConnection m_ActiveConnection ;

        //Bien nay dung de luu tru mot cau noi toi CSDL thong qua OleDbConnection
        //Dung bien nay de thuc hien cac thao tac Doc, ghi, them, sua, xoa du lieu
        private OleDbDataAdapter m_ActiveAdapter;

        //Bien kiem tra xem day co phai la mot danh muc khong
        private Boolean m_IsListTable; 

        //Bien luu tru Caption cua lop. Dung de doc va hien thi
        private String m_DataTableCaption;

        //Bien luu cau lenh duoc ghep noi cau SELECT khi load du lieu
        private String m_SelectCondition;
#endregion

#region " Public properties"
        public string SelectCondition
        {
            get
            {
                   return m_SelectCondition;
            }
            set
            {
                m_SelectCondition = value;
                //m_ActiveAdapter.SelectCommand = this.SetSelectCommand;
                ResetSelectCommand(m_SelectCondition);
            }
        }

        public string DataTableCaption
        {
            get
            {
                return m_DataTableCaption;
            }
            set
            {
                m_DataTableCaption = value;
            }
        }

        public bool IsListTable
        {
            get
            {
                return m_IsListTable;
            }
            set
            {

                m_IsListTable = value;
            }
        }

        public OleDbConnection ActiveConnection 
        {
            get
            {
                return m_ActiveConnection;
            }
            set
            {
                m_ActiveConnection = value;
                m_ActiveAdapter.SelectCommand.Connection= m_ActiveConnection;
                this.InitAffterSetConnection();
            }
        }

        // Summary:
        //     Initializes a new instance of the System.Data.OleDb.OleDbDataAdapter class.
        [DesignerSerializationVisibility(0)]
        [Browsable(false)]
        public  OleDbCommand SelectCommand
        {
            get
            {
                return m_ActiveAdapter.SelectCommand;
            }
        }

        public  OleDbCommand InsertCommand
        {
            get
            {
                if (m_ActiveAdapter.InsertCommand == null) 
                {
                    m_ActiveAdapter.InsertCommand = this.SetInsertCommand();
                }
                
                return m_ActiveAdapter.InsertCommand;
            }
        }

        public  OleDbCommand UpdateCommand 
        {
            get
            {
                if (m_ActiveAdapter.UpdateCommand == null)
                {
                    m_ActiveAdapter.UpdateCommand = this.SetUpdateCommand();
                }
                return m_ActiveAdapter.UpdateCommand;
            }
        }

        //
        // Summary:
        //     Initializes a new instance of the System.Data.DataTable class with the specified
        //     table name.
        //
        // Parameters:
        //   tableName:
        //     The name to give the table. If null or an empty string, a default name is
        //     given when added to the System.Data.DataTableCollection.
        public  OleDbCommand DeleteCommand
        {
            get
            {
                if (m_ActiveAdapter.DeleteCommand == null)
                {
                    m_ActiveAdapter.DeleteCommand = this.SetDeleteCommand();
                }
                return m_ActiveAdapter.DeleteCommand;
            }
        }

        public  Boolean IsDataLoaded 
        {
            get
            {
                return (this.Rows.Count > 0);
            }
        }
#endregion

#region " Override Funtions"

        //Function InitAffterSetConnection
        //Dung de khoi tao cac gia tri, cac bien hay cac cau lenh can phai thuc hien 
        //sau khi set Connection cho lop
        //Su dung tu khoa "virtual" de dinh nghia mot Method cai ma lop ke thua no co the
        // override. Khi lop ke thua muon dinh nghia lai Method nay no phai su dung tu khoa
        // "override"

        protected virtual void InitAffterSetConnection()
        {
            return;
        }

        //Function OthersInit
        //Dung de thuc hien cac khoi tao khac
        //Ham nay co the overridden 
        protected virtual void OthersInit()
        {
            return;
        }
#endregion

#region "Must Override Funtions"

        // Su dung tu khoa "abstract" de bat buoc cac lop ke thua no phai override su dung tu
        // khoa "override" trong cac lop ke thua
        //Cac ham sau day bat buoc phai duoc cai dat lai trong cac lop ke thua

        //Function SetSelectCommand
        //Dung de dat mot cau lenh SELECT cho lop

        
        protected abstract OleDbCommand SetSelectCommand();

        //Function SetInsertCommand
        //Dung de dat mot cau lenh INSERT cho lop
        protected abstract OleDbCommand SetInsertCommand();

        //Function SetUpdateCommand
        //Dung de dat mot cau lenh UPDATE cho lop
        protected abstract OleDbCommand SetUpdateCommand();

        //Function SetDeleteCommand
        //Dung de dat mot cau lenh DELETE cho lop
        protected abstract OleDbCommand SetDeleteCommand();

        //Function SetMappingData
        //Dung de giao tiep giua CSDL thuc su va CSDL cua lop. Gop phan trong viec hien thi giao dien
        // Method nay dung de mapping cac cot khi liet ke duoc trong database vao MyDataTable 
        // no chiu trach nhiem de thuc hien tu dong cac lenh
        protected abstract DataTableMapping SetMappingData();

        //Function SetPrimaryKey
        //Dung de chi ra truong nao cua CSDL cua lop la khoa chinh
        //Se dung de kiem tra tinh hop le khi nhap lieu
        protected abstract DataColumn[] SetPrimaryKey();
#endregion

#region " Class Contructors"
        public MyDataTable (String TableName ): base(TableName)
        {
            m_ActiveAdapter = new OleDbDataAdapter();
            if  (this.SetMappingData() != null)
            {
                m_ActiveAdapter.TableMappings.Add(this.SetMappingData());
            }
            m_ActiveAdapter.SelectCommand = this.SetSelectCommand();
            m_SelectCondition = "";
        }

        public MyDataTable(String TableName ,OleDbConnection ActiveConnection ):this(TableName)
        {
            this.ActiveConnection = ActiveConnection;
        }

        public MyDataTable(String TableName ,String TableCaption ):this(TableName)
        {
            this.DataTableCaption = TableCaption;
        }

        public MyDataTable(String TableName ,String  TableCaption ,OleDbConnection ActiveConnection ):this(TableName, ActiveConnection)
        {
            this.DataTableCaption = TableCaption;
        }
#endregion

#region " Private Functions"
        private bool ResetSelectCommand(string AddedString )
        {
            if (  AddedString.Trim().Length > 0)
            {
                if (m_ActiveAdapter.SelectCommand.CommandText.IndexOf("WHERE") > 0)
                {
                    m_ActiveAdapter.SelectCommand.CommandText += " AND " + AddedString;
                }
                else
                {
                    m_ActiveAdapter.SelectCommand.CommandText += " WHERE " + AddedString;
                }
                return true;
            }
            else
            {
                return false;
            }
        }
#endregion

        
#region " Public Funtions"

        //Kiem tra quan hệ giữa hai kiểu dữ liệu ngày tháng
        //Public Function CheckDateData(ByVal r As System.Data.DataRow, ByVal c1 As String, ByVal c2 As String, ByVal minYears As Integer, ByVal minMonths As Integer, ByVal minDays As Double, ByVal maxYears As Integer, ByVal maxMonths As Integer, ByVal maxDays As Double)
        //    Me.CheckDateData(r, Me.Columns(c1), Me.Columns(c2), minYears, minMonths, minDays, maxYears, maxMonths, maxDays)
        //End Function

        //Public Function CheckDateData(ByVal r As System.Data.DataRow, ByVal c1 As String, ByVal c2 As String, ByVal minYears As Integer, ByVal minMonths As Integer, ByVal minDays As Double)
        //    Me.CheckDateData(r, c1, c2, minYears, minMonths, minDays, minYears, minMonths, minDays)
        //End Function

        //Public Function CheckDateData(ByVal r As System.Data.DataRow, ByVal c1 As System.Data.DataColumn, ByVal c2 As System.Data.DataColumn, ByVal minYears As Integer, ByVal minMonths As Integer, ByVal minDays As Double)
        //    Me.CheckDateData(r, c1, c2, minYears, minMonths, minDays, minYears, minMonths, minDays)
        //End Function

        //Public Function CheckDateData(ByVal r As System.Data.DataRow, ByVal c1 As String, ByVal c2 As String, ByVal minMonths As Integer, ByVal minDays As Double)
        //    Me.CheckDateData(r, Me.Columns(c1), Me.Columns(c2), 0, minMonths, minDays, 0, 0, 0, False)
        //End Function

        //'Public Function CheckDateData(ByVal r As System.Data.DataRow, ByVal c1 As System.Data.DataColumn, ByVal c2 As System.Data.DataColumn, ByVal minYears As Integer, ByVal minMonths As Integer, ByVal minDays As Double, ByVal maxYears As Integer, ByVal maxMonths As Integer, ByVal maxDays As Double, Optional ByVal AutoDate As Boolean = True) As Boolean
        //'    Try
        //'        If r(c2).Equals(DBNull.Value) Then
        //'            r(c2) = Convert.ToDateTime(r(c1)).AddYears(minYears).AddMonths(minMonths).AddDays(minDays)
        //'        Else
        //'            If AutoDate = True Then
        //'                If (Convert.ToDateTime(r(c2)) < Convert.ToDateTime(r(c1)).AddYears(minYears).AddMonths(minMonths).AddDays(minDays)) Or (Convert.ToDateTime(r(c2)) > Convert.ToDateTime(r(c1)).AddYears(maxYears).AddMonths(maxMonths).AddDays(maxDays)) Then
        //'                    XtraMessageBox.show("Giá trị của " & c2.Caption & " phải nằm trong khoảng giá trị đã xác định của " & c1.Caption & ". Chương trình sẽ tự động đặt lại ngày cho " & c2.Caption, "Thông báo", MessageBoxButtons.OK, MessageBoxIcon.Information)
        //'                    r.BeginEdit()
        //'                    r(c2) = Convert.ToDateTime(r(c1)).AddYears(minYears).AddMonths(minMonths).AddDays(minDays)
        //'                    r.EndEdit()
        //'                End If
        //'            ElseIf (Convert.ToDateTime(r(c2)) < Convert.ToDateTime(r(c1)).AddYears(minYears).AddMonths(minMonths).AddDays(minDays)) Then
        //'                XtraMessageBox.show("Giá trị của trường dữ liệu " & c2.Caption & " phải có giá trị lớn hơn hoặc bằng " & Convert.ToDateTime(r(c1)).AddYears(minYears).AddMonths(minMonths).AddDays(minDays) & ". Chương trình sẽ tự động đặt lại ngày cho trường dữ liệu " & c2.Caption, "Thông báo", MessageBoxButtons.OK, MessageBoxIcon.Information)
        //'                r.BeginEdit()
        //'                r(c2) = Convert.ToDateTime(r(c1)).AddYears(minYears).AddMonths(minMonths).AddDays(minDays)
        //'                r.EndEdit()
        //'            End If
        //'        End If
        //'        Return True
        //'    Catch e As Exception
        //'        Return False
        //'    End Try
        //'End Function


        //Tim gia tri theo khoa

        public DataRow FindByPrimaryKey(Object Value ) 
        {
            return this.Rows.Find(Value);
        }

        //Tim gia tri theo mot tap hop cac khoa
        //public DataRow FindByPrimaryKey(Object Value[] )
        //{
        //    return this.Rows.Find(Value);
        //}

        //Tìm gia trị theo giá trị
        //public DataRow FindByValue(string ColumnName ,Object Value ) 
        //{
        //    return this.FindByValue(ColumnName, new Object[] {Value});
        //}
        

        //public DataRow FindByValue(String ColumnNames ,Object Value[] ) 
        //{
        //    try
        //    {
        //        Dim r() As System.Data.DataRow = Me.FindByValues(ColumnNames, Value);
        //        if (r.Length > 0)
        //        {
        //            return r(0);
        //        }
        //        return DBNull;
        //    }
        //    catch (Exception e)
        //    {
        //        return DBNull;
        //    }
        //}

        //Public Function FindByValues(ByVal ColumnName As String, ByVal Value As Object) As DataRow()
        //    Return Me.FindByValues(ColumnName, New Object() {Value})
        //End Function

        //Public Function FindByValues(ByVal ColumnNames As String, ByVal Value() As Object) As DataRow()
        //    Try
        //        Dim sOldSort As String = Me.DefaultView.Sort
        //        Me.DefaultView.Sort = ColumnNames
        //        Dim rv As System.Data.DataRowView() = Me.DefaultView.FindRows(Value)
        //        Me.DefaultView.Sort = sOldSort
        //        Dim r(rv.Length - 1) As System.Data.DataRow
        //        Dim i As Integer
        //        For i = 0 To rv.Length - 1
        //            r(i) = rv(i).Row
        //        Next
        //        If r.Length < 1 Then Return Nothing
        //        Return r
        //    Catch e As Exception
        //        Return Nothing
        //    End Try

        //End Function

        //Load du lieu
        public virtual int LoadData() 
        {
            return this.LoadData(false);
        }

        //Load du lieu theo cau lenh SELECT va kem theo dieu kien SelectCondition
        //SelectCondition:   Chuoi the hien cau dieu kien can phai thuc hien
        public virtual void LoadData(string SelectCondition ) 
        {
            if (ResetSelectCommand(SelectCondition)==true)
            {
                this.LoadData(true);
                //Dat lai cau SELECT ve trang thai ban dau
                m_ActiveAdapter.SelectCommand.CommandText= this.SetSelectCommand().CommandText;
                ResetSelectCommand(m_SelectCondition);
            }
        }

        //Load du lieu voi yeu cau 
        //Du lieu se duoc load khi:
        //Input: Boolean
        //   : Bat buoc phai load neu ReloadData = TRUE
        //   : Tu dong Load khi du lieu khong co ban ghi nao
        //Output:    So ban ghi lay duoc
        public virtual int LoadData(Boolean ReloadData )
        {
            try
            {
                if (m_ActiveConnection == null)
                {
                    //throw new Exception("Chưa kết nối tới CSDL");
                    return -1;
                }
                else
                {
                    if (!(this.IsDataLoaded) || (ReloadData))
                    {
                        if (ReloadData) { this.InitAffterSetConnection();}
                        //Xoa neu co du lieu
                        this.Clear();
                        //Load du lieu
                        m_ActiveAdapter.Fill(this);
                        //Dat khoa 
                        this.PrimaryKey = this.SetPrimaryKey();
                        //Thuc hien cac khoi tao khac
                        this.OthersInit();
                    }
                }
                return this.Rows.Count;
            }
            catch (Exception e)
            {
                //throw new Exception("Lỗi khi thực hiện đọc dữ liệu " + e.Message.ToString());
                Console.WriteLine(e.ToString());
                return -1;
                //throw new Exception("Lỗi khi thực hiện đọc dữ liệu " + e.Message.ToString());
            }
        }

        //Ghi du lieu voi tat ca cac thay doi
        public int  SaveData() 
        {
            return this.SaveData(DataTableAction.AllChanges);
        }

        public virtual int SaveData( DataTableAction DataTableAction) 
        {
            return this.SaveData(DataTableAction, true);
        }
        //Ghi du lieu voi cac thay doi theo dieu kien
        //Input:     PDataTableAction: Kieu ghi du lieu
        //Output:    So ban ghi thay doi
        public virtual int SaveData(DataTableAction DataTableAction,Boolean bShowMessage ) 
        {
            int Results =0;
            DataTable dataChanges = null;
            //Thiet lap cac cau lenh dieu kien
            if (m_ActiveAdapter.InsertCommand == null)
            {
                m_ActiveAdapter.InsertCommand = this.SetInsertCommand();
                m_ActiveAdapter.InsertCommand.Connection = m_ActiveConnection;
            }

            if (m_ActiveAdapter.UpdateCommand == null)
            {
                m_ActiveAdapter.UpdateCommand = this.SetUpdateCommand();
                m_ActiveAdapter.UpdateCommand.Connection = m_ActiveConnection;
            }

            if (m_ActiveAdapter.DeleteCommand == null)
            {
                m_ActiveAdapter.DeleteCommand = this.SetDeleteCommand();
                m_ActiveAdapter.DeleteCommand.Connection = m_ActiveConnection;
                
            }

            
            try
            {
               //Lay cac du lieu thay doi
                switch (DataTableAction)
                {
                    case DataTableAction.AddNew :
                        dataChanges = this.GetChanges(DataRowState.Added);
                        break;
                    case DataTableAction.Update :
                        dataChanges = this.GetChanges(DataRowState.Modified);
                        break;
                    case DataTableAction.Delete :
                        dataChanges = this.GetChanges(DataRowState.Deleted);
                        break;
                    case DataTableAction.AllChanges :
                        dataChanges = this.GetChanges();
                        break;
                }

                if (dataChanges != null)
                {
                    //Ghi du lieu
                    Results = m_ActiveAdapter.Update(dataChanges);
                    this.AcceptChanges();
                    return Results;
                }
                else
                {
                    return 0;
                }
            }
            catch (Exception e)
            {
                if (e.Message.Equals("No error information available: E_FAIL(0x80004005)."))
                {
                    //Me.AcceptChanges()
                    return Results;
                }
                else
                {
                    if (bShowMessage)
                    {
                        String msg ="";
                        switch (DataTableAction)
                        {
                            case DataTableAction.AddNew:
                                msg = "Lỗi khi thêm mới thông tin vào cơ sở dữ liệu. Có thể giá trị khóa bị trùng.";
                                break;
                            case DataTableAction.Update:
                                msg = "Lỗi khi cập nhật thông tin vào cơ sở dữ liệu. Có thể giá trị khóa của bản ghi này đã được sử dụng ở các bản tham chiếu.";
                                break;
                            case DataTableAction.Delete:
                                msg = "Lỗi khi xóa thông tin trong cơ sở dữ liệu. Giá trị khóa đã được sử dụng ở các bảng tham chiếu, không thể xóa được.";
                                break;
                            case DataTableAction.AllChanges:
                                msg = "Lỗi khi thao tác (thêm, sửa, xóa) với thông tin trong cơ sở dữ liệu.";
                                break;
                        }
                        //'XtraMessageBox.Show(msg & ": " & e.Message, "Thông báo", MessageBoxButtons.OK, MessageBoxIcon.Exclamation)
                        Console.WriteLine(msg);
                        //MessageBox.Show(msg, "Thông báo", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
                        //MessageBox.Show("Error: " + e.Message.ToString(), "Thông báo", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
                    }
                    this.RejectChanges();
                    return -1;
                }
            }
        }
#endregion


    }
}
